insert overwrite table jms_dm.dm_fr_tyd_departure_transfer_timely_route_summary_dt partition (dt)
select nvl(t1.start_code, null)                                                                   as start_code,
       nvl(max(t1.start_name), null)                                                              as start_name,
       nvl(max(t1.order_source_code), null)                                                       as order_source_code,
       nvl(t1.order_source_name, null)                                                            as order_source_name,
       nvl(t1.center_type, null)                                                                  as center_type,
       nvl(max(t1.start_agent_code), null)                                                        as start_agent_code,
       nvl(max(t1.start_agent_name), null)                                                        as start_agent_name,
       sum(1)                                                                                     as send_account,
       sum(if(t1.is_timely = 2, 1, 0))                                                            as route_lack_account,
       sum(if(t1.is_timely = 1, 1, 0))                                                            as transfer_intime_account,
       sum(if(t1.is_timely = 1, 1, 0)) / (sum(1) - sum(if(is_timely = 2, 1, 0)) -
                                          sum(if(untimely_cause = 2, 1, 0)))                      as transfer_intime_rate,
       sum(if(t1.untimely_cause = 7, 1, 0))                                                       as without_arrival_account,
       sum(if(t1.untimely_cause = 2, 1, 0))                                                       as error_send_account,
       sum(if(t1.untimely_cause = 5, 1, 0))                                                       as opp_delay_account,
       sum(if(t1.untimely_cause = 4, 1, 0))                                                       as send_delay_account,
       sum(if(t1.untimely_cause = 3, 1, 0))                                                       as shipment_delay_account,
       sum(if(t1.untimely_cause = 6, 1, 0))                                                       as stop_delay_account,
       sum(if(t1.all_line_is_timely = 1, 1, 0))                                                   as all_line_transfer_intime_account,
       sum(if(t1.all_line_is_timely = 1, 1, 0)) / (sum(1) - sum(if(t1.all_line_is_timely = 2, 1, 0)) -
                                                   sum(if(t1.all_line_untimely_cause = 2, 1, 0))) as all_line_transfer_intime_rate,
       sum(if(untimely_cause = 9, 1, 0))                                                          as shipment_error_account,
       nvl(t1.mage_region_code, null)                                                             as mage_region_code,
       nvl(max(t1.mage_region_name), null)                                                        as mage_region_name,
       t1.dt                                                                                      as date_time,
       t1.dt
from jms_dm.dm_fr_tyd_departure_transfer_timely_route_detail_dt t1
where dt = '{{ execution_date | cst_ds }}'
group by start_code
       , order_source_name
       , dt
       , center_type
       , mage_region_code
;